import pymysql

db = pymysql.connect(host="localhost", user="root", password="123456",database="testdb")
cursor = db.cursor()
# 1 创建数据库
# cursor.execute("create database TESTDB")

# cursor.execute("SELECT VERSION()")
# data = cursor.fetchone()
# print("Database version : %s "%data)

# 2 创建表
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#
# sql = """
#     CREATE TABLE EMPLOYEE(
#         FIRST_NAME CHAR(20) NOT NULL,
#         LAST_NAME CHAR(20),
#         AGE INT,
#         SEX CHAR(1),
#         INCOME FLOAT)
# """
# cursor.execute(sql)

# 3 插入数据
# sql = """
#     INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
#     VALUES('Mac','Mohan',20,'M',13000)
# """
# try:
#     cursor.execute(sql)
#     db.commit()
# except:
#     db.rollback()
# finally:
#     db.close()


# 4 数据库查询操作

# sql = "SELECT * FROM EMPLOYEE" \
#       " WHERE INCOME > %s" % (10000)
# try:
#       cursor.execute(sql)
#       fetchall = cursor.fetchall()
#       for row in fetchall:
#             fname = row[0]
#             lname = row[1]
#             age = row[2]
#             sex = row[3]
#             income = row[4]
#             print('fname=%s,lname=%s,age=%s,sex=%s,income=%s'%
#                   (fname,lname,age,sex,income))
# except:
#       print("Error: unable to fetch data")
#       raise
# db.close()

# 5 数据库更新操作
#
# sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'"%('M')
# try:
#       cursor.execute(sql)
#       db.commit()
# except:
#       db.rollback()
# db.close()

# 6 删除操作
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s "%(20)
try:
      cursor.execute(sql)
      db.commit()
except:
      db.rollback()
finally:
      db.close()